package cn.conac.rc.ofs.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;
import cn.conac.rc.ofs.entity.OrganizationBaseEntity;

@Repository
public interface OrganizationBaseRepository extends GenericDao<OrganizationBaseEntity, Integer> {

	/**
	 * 通过OrgId获取Org表对应BaseId，然后获取对应Base表信息
	 * @param orgId
	 * @return OrganizationBaseEntity
	 */
	@Query(value ="select base.* from OFS_ORGANIZATION_BASE base, OFS_ORGANIZATION org where org.base_Id = base.id and org.id=?1",nativeQuery = true)
	OrganizationBaseEntity findByOrgId(@Param("orgId") Integer orgId);
	
	/**
	 * 通过OrgId获取Org表对应BaseId，然后获取对应Base表信息
	 * @param areaId
	 * @return List<OrganizationBaseEntity>
	 */
	@Query(value ="select base.* from OFS_ORGANIZATION_BASE base, ofs_org_status org_stat where base.id = org_stat.id  and org_stat.status='00' and  base.area_id=:areaId order by org_stat.update_time desc",nativeQuery = true)
	List<OrganizationBaseEntity> findPreRealseList(@Param("areaId") String areaId);

	/**
	 * 通过OrgId获取状态在【待提交】【待审核】【驳回】的Base表信息
	 * @param orgId
	 * @return List<OrganizationBaseEntity>
	 */
	@Query("select ob from OrganizationBaseEntity ob, OrgStatusEntity os where os.id = ob.id and os.status in ('00','01','02') and os.isHistory='0' and ob.orgId = :orgId order by os.updateTime desc, ob.name asc")
	List<OrganizationBaseEntity> findOrgListByOrgId4Check(@Param("orgId") Integer orgId);

	/**
	 *  通过OrgId获取对应compId的Base表信息（即获取机构ID为OrgId的子机构Base信息）
	 * @param orgId
	 * @return List<OrganizationBaseEntity>
	 */
	@Query("select ob from OrganizationBaseEntity ob where ob.compId = :orgId")
	List<OrganizationBaseEntity> findChildOrgListByOrgId(@Param("orgId") Integer orgId);
	
	
	/**
	 * 通过OrgId获取Org表对应BaseId，然后获取对应Base表信息
	 * @param orgId
	 * @return OrganizationBaseEntity
	 */
	@Query(value ="select base.* from OFS_ORGANIZATION_BASE base  where  base.org_id=?1",nativeQuery = true)
	List<OrganizationBaseEntity> findListByOrgId(@Param("orgId") Integer orgId);
}
